package nckd.wnqjt.tmc.tda.plugin.form;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import kd.bos.algo.*;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.dataentity.entity.DynamicObjectCollection;
import kd.bos.db.DB;
import kd.bos.db.DBRoute;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.FilterInfo;
import kd.bos.entity.report.FilterItemInfo;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.orm.ORM;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import kd.bos.servicehelper.QueryServiceHelper;
import kd.bos.servicehelper.operation.SaveServiceHelper;
import org.apache.commons.collections.CollectionUtils;

/**
 * @author husheng
 * @date 2024-11-04 20:08
 * @description 授信担保
 */
public class CreditGuaranteeListDataPlugin extends AbstractReportListDataPlugin {
    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) {
        Date currentDate = this.getCurrentDate();

        QFilter filter1 = this.buildFilter(reportQueryParam);
        // Tyx 2025-04-02 注释 改成取单据上授信协议号
        //String creditlimitSql = "id,number nckd_creditnumber,org,org.name nckd_orgname,bank,startdate,enddate";
        String creditlimitSql = "id,number nckd_creditnumber,contractno nckd_contractno,org,org.name nckd_orgname,bank,startdate,enddate";
        // 授信额度管理
        DataSet cfmCreditlimit = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cfm_creditlimit", creditlimitSql, filter1.toArray(), null);

        // 合作金融机构
        DataSet bdFinorginfo = QueryServiceHelper.queryDataSet(this.getClass().getName(), "bd_finorginfo", "id,bank_cate,bank_cate.name", new QFilter[]{new QFilter("status", QCP.equals, "C").and("enable", QCP.equals, "1")}, null);

        DataSet ceditlimit = cfmCreditlimit.join(bdFinorginfo).on("bank", "id").select(cfmCreditlimit.getRowMeta().getFieldNames(), new String[]{"bank_cate.name nckd_bank"}).finish();

        // 其中银行贷款	流动资金贷款
        ceditlimit = this.setBdFinorginfoAmount(currentDate, "SX0002", ceditlimit);
        // 银行敞口	银行承兑汇票
        ceditlimit = this.setBdFinorginfoAmount(currentDate, "SX0003", ceditlimit);
        // 合计
        ceditlimit = ceditlimit.addField("nckd_amount1 + nckd_amount2", "nckd_sum1");

        /**
         * 有担保有债务
         * 有担保无债务
         * 有债务无担保
         */

        // 其中：银行贷款使用额
        DataSet dataSet1 = this.setAmount1(ceditlimit.copy());
        // 银行敞口使用额
        DataSet dataSet2 = this.setAmount2(dataSet1);

        DataSet dataSet = dataSet2;


        DynamicObject test = BusinessDataServiceHelper.newDynamicObject("nckd_testdocuments");
        test.set("billno", "test-A-" + new SimpleDateFormat("yyyyMMddHHmm").format(new Date()));
        test.set("billstatus", "A");
        DynamicObjectCollection entryentity = test.getDynamicObjectCollection("entryentity");
        //打印单据
        DataSet recCopy = dataSet.copy();
        while (recCopy.hasNext()) {
            DynamicObject entry = entryentity.addNew();
            Row row = recCopy.next();
            entry.set("nckd_name", "授信担保");
            entry.set("nckd_number", row.get("nckd_creditnumber"));
            entry.set("nckd_amount", 0);
            entry.set("nckd_text", "");
        }
        SaveServiceHelper.save(new DynamicObject[]{test});


        // 授信期间
        dataSet = dataSet.addField("to_char(startdate,'yyyy.MM.dd') + ' - ' + to_char(enddate,'yyyy.MM.dd')", "nckd_creditperiod");

        // 建材集团担保
        DataSet dataSet3 = this.setGuaranteeinfoAmount("1", dataSet.copy());
        // 股份公司担保
        dataSet3 = this.setGuaranteeinfoAmount("2", dataSet3);
        // 南方万年青担保
        dataSet3 = this.setGuaranteeinfoAmount("3", dataSet3);

        dataSet3 = dataSet3.addNullField("nckd_orgamount4", "nckd_otherorgname");

        // 子公司之间担保
        DataSet dataSet4 = this.setGuaranteeotherinfoAmount(dataSet.copy());

        dataSet = dataSet3.union(dataSet4);

        DataSet notEmptyDataSet4 = dataSet.filter("nckd_orgamount1 is not null or nckd_orgamount2 is not null or nckd_orgamount3 is not null or nckd_orgamount4 is not null");
        DataSet emptyDataSet4 = dataSet.filter("nckd_orgamount1 is null and nckd_orgamount2 is null and nckd_orgamount3 is null and nckd_orgamount4 is null").distinct();// 有债务无担保
        dataSet = notEmptyDataSet4.union(emptyDataSet4);


        DynamicObject test2 = BusinessDataServiceHelper.newDynamicObject("nckd_testdocuments");
        test2.set("billno", "test-B-" + new SimpleDateFormat("yyyyMMddHHmm").format(new Date()));
        test2.set("billstatus", "A");
        DynamicObjectCollection entryentity2 = test2.getDynamicObjectCollection("entryentity");
        //打印单据
        DataSet recCopy2 = dataSet.copy();
        while (recCopy2.hasNext()) {
            DynamicObject entry = entryentity2.addNew();
            Row row = recCopy2.next();
            entry.set("nckd_name", "授信担保");
            entry.set("nckd_number", row.get("nckd_creditnumber"));
            entry.set("nckd_amount", 0);
            entry.set("nckd_text", "");
        }
        SaveServiceHelper.save(new DynamicObject[]{test2});


        // 其他方式（抵押、质押等） 担保合同编号	担保期间
        dataSet = this.setGuaranteeinfo(dataSet);

        ORM orm1 = ORM.create();
        DynamicObjectCollection dy1 = orm1.toPlainDynamicObjectCollection(dataSet.copy());
        Set<String> creditnumberList = dy1.stream().map(t1 -> t1.getString("nckd_creditnumber")).collect(Collectors.toSet());

        List<DynamicObject> collect = new ArrayList<>();

        creditnumberList.stream().forEach(t -> {
            // 无担保
            List<DynamicObject> list1 = dy1.stream().filter(t1 -> {
                if (t.equals(t1.getString("nckd_creditnumber"))) {
                    BigDecimal nckdOrgamount1 = t1.getBigDecimal("nckd_orgamount1");
                    BigDecimal nckdOrgamount2 = t1.getBigDecimal("nckd_orgamount2");
                    BigDecimal nckdOrgamount3 = t1.getBigDecimal("nckd_orgamount3");
                    BigDecimal nckdOrgamount4 = t1.getBigDecimal("nckd_orgamount4");
                    if ((nckdOrgamount1 == null || nckdOrgamount1.compareTo(BigDecimal.ZERO) == 0) &&
                            (nckdOrgamount2 == null || nckdOrgamount2.compareTo(BigDecimal.ZERO) == 0) &&
                            (nckdOrgamount3 == null || nckdOrgamount3.compareTo(BigDecimal.ZERO) == 0) &&
                            (nckdOrgamount4 == null || nckdOrgamount4.compareTo(BigDecimal.ZERO) == 0)) {
                        return true;
                    }
                }
                return false;
            }).collect(Collectors.toList());

            // 有担保
            List<DynamicObject> list2 = dy1.stream().filter(t1 -> {
                if (t.equals(t1.getString("nckd_creditnumber"))) {
                    BigDecimal nckdOrgamount1 = t1.getBigDecimal("nckd_orgamount1");
                    BigDecimal nckdOrgamount2 = t1.getBigDecimal("nckd_orgamount2");
                    BigDecimal nckdOrgamount3 = t1.getBigDecimal("nckd_orgamount3");
                    BigDecimal nckdOrgamount4 = t1.getBigDecimal("nckd_orgamount4");
                    if ((nckdOrgamount1 != null && nckdOrgamount1.compareTo(BigDecimal.ZERO) > 0) ||
                            (nckdOrgamount2 != null && nckdOrgamount2.compareTo(BigDecimal.ZERO) > 0) ||
                            (nckdOrgamount3 != null && nckdOrgamount3.compareTo(BigDecimal.ZERO) > 0) ||
                            (nckdOrgamount4 != null && nckdOrgamount4.compareTo(BigDecimal.ZERO) > 0)) {
                        return true;
                    }
                }
                return false;
            }).collect(Collectors.toList());

            if (CollectionUtils.isNotEmpty(list2)) {
                collect.addAll(list2);
            } else {
                collect.addAll(list1);
            }
        });

        DynamicObjectCollection dynamicObjects = new DynamicObjectCollection();
        dynamicObjects.addAll(collect);

        dataSet = buildDataByObjCollection("algoKey", dataSet.getRowMeta().getFields(), dynamicObjects);


        DynamicObject test3 = BusinessDataServiceHelper.newDynamicObject("nckd_testdocuments");
        test3.set("billno", "test-C-" + new SimpleDateFormat("yyyyMMddHHmm").format(new Date()));
        test3.set("billstatus", "A");
        DynamicObjectCollection entryentity3 = test3.getDynamicObjectCollection("entryentity");
        //打印单据
        DataSet recCopy3 = dataSet.copy();
        while (recCopy3.hasNext()) {
            DynamicObject entry = entryentity3.addNew();
            Row row = recCopy3.next();
            entry.set("nckd_name", "授信担保");
            entry.set("nckd_number", row.get("nckd_creditnumber"));
            entry.set("nckd_amount", 0);
            entry.set("nckd_text", "");
        }
        SaveServiceHelper.save(new DynamicObject[]{test3});


        //筛选出 筛选器传入的ids
        Map<String, Object> customParam = reportQueryParam.getCustomParam();
        List<Long> longList = null;
        if (!customParam.isEmpty()) {
            List<String> list = (ArrayList<String>) customParam.get("id");
            if (CollectionUtils.isNotEmpty(list)) {
                longList = list.stream().map(e -> Long.valueOf(e)).collect(Collectors.toList());
            }
        }

        if (CollectionUtils.isNotEmpty(longList)) {
            dataSet = dataSet.filter("gcontract in ids", Collections.singletonMap("ids", longList));
        }

        // 在有债务无担保时担保比例为0，因此担保比例按100%计算
        ORM orm = ORM.create();
        DynamicObjectCollection dy = orm.toPlainDynamicObjectCollection(dataSet.copy());
        dy.stream().forEach(t -> {
            if ((Objects.isNull(t.get("a_gratio1")) || t.getLong("a_gratio1") == 0) &&
                    (Objects.isNull(t.get("a_gratio2")) || t.getLong("a_gratio2") == 0) &&
                    (Objects.isNull(t.get("a_gratio3")) || t.getLong("a_gratio3") == 0) &&
                    (Objects.isNull(t.get("a_gratio4")) || t.getLong("a_gratio4") == 0)) {
                t.set("a_gratio1", 100);
            }
        });
        dataSet = buildDataByObjCollection("algoKey", dataSet.getRowMeta().getFields(), dy);

        dataSet = dataSet.select(new String[]{"nckd_orgname", "nckd_bank", "(nckd_sum1 / 10000) nckd_sum1", "(nckd_amount1 / 10000) nckd_amount1", "(nckd_amount2 / 10000) nckd_amount2",
                "(nckd_amount3 * (a_gratio1 + a_gratio2 + a_gratio3 + a_gratio4) / 100 / 10000 + nckd_amount4 * (a_gratio1 + a_gratio2 + a_gratio3 + a_gratio4) / 100 / 10000) nckd_sum2", "(nckd_amount3 * (a_gratio1 + a_gratio2 + a_gratio3 + a_gratio4) / 100 /10000) nckd_amount3",
                "(nckd_amount4 * (a_gratio1 + a_gratio2 + a_gratio3 + a_gratio4) / 100 / 10000) nckd_amount4", "nckd_creditnumber", "nckd_contractno", "nckd_creditperiod", "loanbillId", "payablebillId",
                "(nckd_orgamount1 * a_gratio1 / 100 / 10000) nckd_orgamount1", "(nckd_orgamount2 * a_gratio2 / 100 / 10000) nckd_orgamount2", "(nckd_orgamount3 * a_gratio3 / 100 / 10000) nckd_orgamount3", "(nckd_orgamount4 * a_gratio4 / 100 / 10000) nckd_orgamount4",
                "nckd_otherorgname", "nckd_othertype", "nckd_guaranteenumber", "nckd_guaranteeperiod", "1 seq"});

        DataSet finish1 = dataSet.copy().select("nckd_orgname", "nckd_sum1", "nckd_amount1", "nckd_amount2", "nckd_creditnumber", "nckd_contractno").distinct()
                .groupBy(new String[]{"nckd_orgname"})
                .sum("nckd_sum1").sum("nckd_amount1").sum("nckd_amount2").finish();

//        DataSet finish2 = dataSet.copy().select("nckd_orgname", "nckd_orgamount1", "nckd_orgamount2", "nckd_orgamount3", "loanbillId", "payablebillId").distinct()
//                .groupBy(new String[]{"nckd_orgname"})
//                .sum("nckd_orgamount1").sum("nckd_orgamount2").sum("nckd_orgamount3").finish();

        // 根据借款单位算合计
        DataSet dataSetSum = dataSet.copy().groupBy(new String[]{"nckd_orgname"}).sum("nckd_sum2").sum("nckd_amount3").sum("nckd_amount4")
                .sum("nckd_orgamount1").sum("nckd_orgamount2").sum("nckd_orgamount3").sum("nckd_orgamount4").finish();
        dataSetSum = dataSetSum.join(finish1).on("nckd_orgname", "nckd_orgname").select(dataSetSum.getRowMeta().getFieldNames(), new String[]{"nckd_sum1", "nckd_amount1", "nckd_amount2"}).finish();
//        dataSetSum = dataSetSum.join(finish2).on("nckd_orgname", "nckd_orgname").select(dataSetSum.getRowMeta().getFieldNames(), new String[]{"nckd_orgamount1", "nckd_orgamount2", "nckd_orgamount3"}).finish();

        dataSetSum = dataSetSum.select(new String[]{"nckd_orgname", "'合计' as nckd_bank", "nckd_sum1", "nckd_amount1", "nckd_amount2",
                "nckd_sum2", "nckd_amount3", "nckd_amount4", "null nckd_creditnumber", "null nckd_contractno", "null nckd_creditperiod", "null loanbillId",
                "null payablebillId", "nckd_orgamount1", "nckd_orgamount2", "nckd_orgamount3", "nckd_orgamount4", "null nckd_otherorgname",
                "null nckd_othertype", "null nckd_guaranteenumber", "null nckd_guaranteeperiod", "2 seq"});

        DataSet dataSetUnion = dataSet.union(dataSetSum).orderBy(new String[]{"nckd_orgname", "seq", "nckd_creditnumber", "nckd_guaranteenumber"});

        // 总计
        DataSet ds = dataSetSum.copy().groupBy(null).sum("nckd_sum1").sum("nckd_amount1").sum("nckd_amount2")
                .sum("nckd_sum2").sum("nckd_amount3").sum("nckd_amount4")
                .sum("nckd_orgamount1").sum("nckd_orgamount2").sum("nckd_orgamount3").sum("nckd_orgamount4").finish();
        ds = ds.select(new String[]{"'-' as nckd_orgname", "'总计' as nckd_bank", "nckd_sum1", "nckd_amount1", "nckd_amount2",
                "nckd_sum2", "nckd_amount3", "nckd_amount4", "null nckd_creditnumber", "null nckd_contractno", "null nckd_creditperiod", "null loanbillId",
                "null payablebillId", "nckd_orgamount1", "nckd_orgamount2", "nckd_orgamount3", "nckd_orgamount4", "null nckd_otherorgname",
                "null nckd_othertype", "null nckd_guaranteenumber", "null nckd_guaranteeperiod", "3 as seq"});

        DataSet finish = dataSetUnion.union(ds);

        return finish;
    }

    /**
     * 设置 其中：银行贷款使用额
     *
     * @param ceditlimit
     * @return
     */
    private DataSet setAmount1(DataSet ceditlimit) {
        // 提款业务状态是已提款或已部分还款
        QFilter filter2 = new QFilter("billstatus", QCP.equals, "C")
                .and("datasource", QCP.equals, "cfm")
                .and("loantype", QCP.in, new String[]{"loan", "sl"})
                .and("drawtype", QCP.in, new String[]{"drawed", "partpayment"});
        String loanbillSql = "id,creditlimit,billno";
        // 银行提款单
        DataSet cfmLoanbill = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cfm_loanbill", loanbillSql, filter2.toArray(), null);

        // 授信额度占用单
        DataSet cfmCredituse = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cfm_credituse", "id,sourcebillid,creditlimit,realamt", new QFilter[]{new QFilter("billstatus", QCP.equals, "C").and("isrelease", QCP.equals, "0")}, null);
        DataSet cfmLoanbill1 = cfmLoanbill.copy().leftJoin(cfmCredituse).on("id", "sourcebillid").select(new String[]{"id", "creditlimit"}, new String[]{"realamt"}).finish();

        DataSet dataSet1 = ceditlimit.copy().leftJoin(cfmLoanbill1).on("id", "creditlimit").select(ceditlimit.getRowMeta().getFieldNames(), new String[]{"id as loanbillId", "realamt as nckd_amount3"}).finish();

        // 担保债务登记
        QFilter qFilter = new QFilter("billstatus", QCP.equals, "C")
                .and("businesscode", QCP.like, "D-%");
        DataSet gmDebtRegister = QueryServiceHelper.queryDataSet(this.getClass().getName(), "gm_debt_register", "id,businesscode,amount", qFilter.toArray(), null);
        DataSet debtRegister = cfmLoanbill.copy().join(gmDebtRegister).on("billno", "businesscode").select(new String[]{"creditlimit"}, new String[]{"id", "amount"}).finish();

        DataSet dataSet2 = ceditlimit.copy().join(debtRegister).on("id", "creditlimit").select(ceditlimit.getRowMeta().getFieldNames(), new String[]{"id as loanbillId", "amount as nckd_amount3"}).finish();

        return dataSet1.union(dataSet2);
    }

    /**
     * 设置 银行敞口使用额
     *
     * @param ceditlimit
     * @return
     */
    private DataSet setAmount2(DataSet ceditlimit) {
        // 票据状态是已登记且存在授信的开票
        QFilter filter3 = new QFilter("rptype", QCP.equals, "paybill")
                .and("billstatus", QCP.equals, "C")
                .and("draftbillstatus", QCP.equals, "registered")
                .and("creditlimit", QCP.not_equals, 0);
        String payablebillSql = "id,creditlimit,creditamount,billno";
        // 应付票据
        DataSet cdmPayablebill = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cdm_payablebill", payablebillSql, filter3.toArray(), null);

        DataSet dataSet1 = ceditlimit.copy().leftJoin(cdmPayablebill.copy()).on("id", "creditlimit").select(ceditlimit.getRowMeta().getFieldNames(), new String[]{"id as payablebillId", "creditamount as nckd_amount4"}).finish();

        // 担保债务登记
        QFilter qFilter = new QFilter("billstatus", QCP.equals, "C")
                .and("businesscode", QCP.like, "CDP-%");
        DataSet gmDebtRegister = QueryServiceHelper.queryDataSet(this.getClass().getName(), "gm_debt_register", "id,businesscode,amount", qFilter.toArray(), null);
        DataSet debtRegister = cdmPayablebill.copy().join(gmDebtRegister).on("billno", "businesscode").select(new String[]{"creditlimit"}, new String[]{"id", "amount"}).finish();

        DataSet dataSet2 = ceditlimit.copy().join(debtRegister).on("id", "creditlimit").select(ceditlimit.getRowMeta().getFieldNames(), new String[]{"id as payablebillId", "amount as nckd_amount4"}).finish();

        return dataSet1.union(dataSet2);
    }

    /**
     * 设置 其他方式（抵押、质押等） 担保合同编号	担保期间
     *
     * @param ds
     * @return
     */
    private DataSet setGuaranteeinfo(DataSet ds) {
        // 担保占用
        String guaranteeuseSql = "id,gsrcbillid,gcontract,gcontract.billno,gcontract.guaranteeno,gcontract.begindate,gcontract.enddate,gratio,gcontract.guaranteeway,gcontract.entry_guaranteeorg.a_gratio a_gratio,gcontract.entry_guaranteeorg.a_guaranteeorgtext a_guaranteeorgtext";
        // kd.tmc.fbp.formplugin.common.guaranteeuse.GuaranteeUseBizEntryPlugin.loadGContract
        DataSet gmGuaranteeuse = QueryServiceHelper.queryDataSet(this.getClass().getName(), "gm_guaranteeuse", guaranteeuseSql, new QFilter[]{new QFilter("billstatus", QCP.equals, "C")}, null);

        // 根据担保方式分类  存在多种担保方式，优先级：抵押>质押>保证，即一份担保合同担保方式存在抵押和保证，数据全部归属到抵押；
        DataSet ensure = gmGuaranteeuse.copy().filter("gcontract.guaranteeway not like concat('%','mortgage','%') and gcontract.guaranteeway not like concat('%','pledge','%')");
        DataSet mortgage = gmGuaranteeuse.copy().filter("gcontract.guaranteeway like concat('%','mortgage','%')");
        DataSet pledge = gmGuaranteeuse.copy().filter("gcontract.guaranteeway not like concat('%','mortgage','%') and gcontract.guaranteeway like concat('%','pledge','%')");

        //Tyx 2025-04-02 担保方式为保证时不显示
        ensure = ensure.addField("' '", "nckd_othertype");
        //ensure = ensure.addField("'保证'", "nckd_othertype");
        mortgage = mortgage.addField("'抵押'", "nckd_othertype");
        pledge = pledge.addField("'质押'", "nckd_othertype");

        DataSet guaranteeuse = ensure.union(mortgage, pledge);

        DataSet dataSet1 = ds.copy().filter("loanbillId is not null");
        DataSet dataSet2 = ds.copy().filter("payablebillId is not null");
        DataSet dataSet3 = ds.copy().filter("loanbillId is null and payablebillId is null"); // 有担保无债务

        /**
         * guaranteeorgtext1  建材集团
         * guaranteeorgtext2  股份公司
         * guaranteeorgtext3  南方万年青
         * nckd_otherorgname  其他子公司
         */
        // 银行提款
        DataSet dataSet11 = dataSet1.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is null and guaranteeorgtext3 is null");
        DataSet dataSet12 = dataSet1.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is not null and guaranteeorgtext3 is null");
        DataSet dataSet13 = dataSet1.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is null and guaranteeorgtext3 is not null");
        DataSet dataSet14 = dataSet1.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is not null and guaranteeorgtext3 is null");
        DataSet dataSet15 = dataSet1.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is null and guaranteeorgtext3 is not null");
        DataSet dataSet16 = dataSet1.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is not null and guaranteeorgtext3 is not null");
        DataSet dataSet17 = dataSet1.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is not null and guaranteeorgtext3 is not null");
        DataSet dataSet18 = dataSet1.copy().filter("nckd_otherorgname is not null");
        DataSet dataSet19 = dataSet1.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is null and guaranteeorgtext3 is null and nckd_otherorgname is null"); // 无担保

        dataSet11 = dataSet11.leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet12 = dataSet12.leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "a_gratio as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet13 = dataSet13.leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet14 = dataSet14.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1"}).finish();
        dataSet14 = dataSet14.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(dataSet14.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet15 = dataSet15.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1", "0 as a_gratio2"}).finish();
        dataSet15 = dataSet15.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet15.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet16 = dataSet16.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "a_gratio as a_gratio2"}).finish();
        dataSet16 = dataSet16.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet16.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet17 = dataSet17.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1"}).finish();
        dataSet17 = dataSet17.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(dataSet17.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio2"}).finish();
        dataSet17 = dataSet17.copy().leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet17.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet18 = dataSet18.leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").on("nckd_otherorgname", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "a_gratio as a_gratio4", "nckd_othertype"}).finish();
        dataSet19 = dataSet19.leftJoin(guaranteeuse).on("loanbillId", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();

        dataSet1 = dataSet11.union(dataSet12, dataSet13, dataSet14, dataSet15, dataSet16, dataSet17, dataSet18, dataSet19);

        // 开票登记
        DataSet dataSet21 = dataSet2.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is null and guaranteeorgtext3 is null");
        DataSet dataSet22 = dataSet2.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is not null and guaranteeorgtext3 is null");
        DataSet dataSet23 = dataSet2.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is null and guaranteeorgtext3 is not null");
        DataSet dataSet24 = dataSet2.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is not null and guaranteeorgtext3 is null");
        DataSet dataSet25 = dataSet2.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is null and guaranteeorgtext3 is not null");
        DataSet dataSet26 = dataSet2.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is not null and guaranteeorgtext3 is not null");
        DataSet dataSet27 = dataSet2.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is not null and guaranteeorgtext3 is not null");
        DataSet dataSet28 = dataSet2.copy().filter("nckd_otherorgname is not null");
        DataSet dataSet29 = dataSet2.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is null and guaranteeorgtext3 is null and nckd_otherorgname is null"); // 无担保

        dataSet21 = dataSet21.leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet22 = dataSet22.leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "a_gratio as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet23 = dataSet23.leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet24 = dataSet24.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1"}).finish();
        dataSet24 = dataSet24.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(dataSet24.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet25 = dataSet25.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1", "0 as a_gratio2"}).finish();
        dataSet25 = dataSet25.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet25.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet26 = dataSet26.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "a_gratio as a_gratio2"}).finish();
        dataSet26 = dataSet26.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet26.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet27 = dataSet27.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1"}).finish();
        dataSet27 = dataSet27.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(dataSet27.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio2"}).finish();
        dataSet27 = dataSet27.copy().leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet27.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet28 = dataSet28.leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").on("nckd_otherorgname", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "a_gratio as a_gratio4", "nckd_othertype"}).finish();
        dataSet29 = dataSet29.leftJoin(guaranteeuse).on("payablebillId", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();

        dataSet2 = dataSet21.union(dataSet22, dataSet23, dataSet24, dataSet25, dataSet26, dataSet27, dataSet28, dataSet29);

        // 无债务
        DataSet dataSet31 = dataSet3.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is null and guaranteeorgtext3 is null");
        DataSet dataSet32 = dataSet3.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is not null and guaranteeorgtext3 is null");
        DataSet dataSet33 = dataSet3.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is null and guaranteeorgtext3 is not null");
        DataSet dataSet34 = dataSet3.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is not null and guaranteeorgtext3 is null");
        DataSet dataSet35 = dataSet3.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is null and guaranteeorgtext3 is not null");
        DataSet dataSet36 = dataSet3.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is not null and guaranteeorgtext3 is not null");
        DataSet dataSet37 = dataSet3.copy().filter("guaranteeorgtext1 is not null and guaranteeorgtext2 is not null and guaranteeorgtext3 is not null");
        DataSet dataSet38 = dataSet3.copy().filter("nckd_otherorgname is not null");
        DataSet dataSet39 = dataSet3.copy().filter("guaranteeorgtext1 is null and guaranteeorgtext2 is null and guaranteeorgtext3 is null and nckd_otherorgname is null"); // 无担保

        dataSet31 = dataSet31.leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet32 = dataSet32.leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "a_gratio as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet33 = dataSet33.leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet34 = dataSet34.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1"}).finish();
        dataSet34 = dataSet34.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(dataSet34.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet35 = dataSet35.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1", "0 as a_gratio2"}).finish();
        dataSet35 = dataSet35.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet35.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet36 = dataSet36.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "a_gratio as a_gratio2"}).finish();
        dataSet36 = dataSet36.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet36.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet37 = dataSet37.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext1", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "a_gratio as a_gratio1"}).finish();
        dataSet37 = dataSet37.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext2", "a_guaranteeorgtext").select(dataSet37.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio2"}).finish();
        dataSet37 = dataSet37.copy().leftJoin(guaranteeuse).on("id", "gsrcbillid").on("guaranteeorgtext3", "a_guaranteeorgtext").select(dataSet37.getRowMeta().getFieldNames(), new String[]{"a_gratio as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();
        dataSet38 = dataSet38.leftJoin(guaranteeuse).on("id", "gsrcbillid").on("nckd_otherorgname", "a_guaranteeorgtext").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "a_gratio as a_gratio4", "nckd_othertype"}).finish();
        dataSet39 = dataSet39.leftJoin(guaranteeuse).on("id", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gcontract", "gcontract.guaranteeno as nckd_guaranteenumber", "to_char(gcontract.begindate,'yyyy.MM.dd') + ' - ' + to_char(gcontract.enddate,'yyyy.MM.dd') as nckd_guaranteeperiod", "0 as a_gratio1", "0 as a_gratio2", "0 as a_gratio3", "0 as a_gratio4", "nckd_othertype"}).finish();

        dataSet3 = dataSet31.union(dataSet32, dataSet33, dataSet34, dataSet35, dataSet36, dataSet37, dataSet38, dataSet39);

        return dataSet1.union(dataSet2, dataSet3).distinct();
    }

    /**
     * 设置 子公司之间担保金额 	子公司之间担保
     *
     * @param ds
     * @return
     */
    private DataSet setGuaranteeotherinfoAmount(DataSet ds) {
        ds = ds.addNullField("nckd_orgamount1", "guaranteeorgtext1", "nckd_orgamount2", "guaranteeorgtext2", "nckd_orgamount3", "guaranteeorgtext3");

        DynamicObject[] dynamicObjects = BusinessDataServiceHelper.load("bos_org", "name", new QFilter[]{new QFilter("number", QCP.in, new String[]{"C0", "C01", "C010301"})});
        List<String> nameList = Arrays.stream(dynamicObjects).map(t -> t.getString("name")).collect(Collectors.toList());

        QFilter qFilter = new QFilter("gcontract.entry_guaranteeorg.a_guaranteeorgtext", QCP.not_in, nameList);
//                .and("gsrcbilltype", QCP.not_equals, "cfm_creditlimit");
        // 担保占用
        String guaranteeuseSql = "id,gsrcbillid,gcontract,gcontract.entry_guaranteeorg.a_guaranteeorgtext a_guaranteeorgtext,gamount,gratio,gdebtamount";
        // kd.tmc.fbp.formplugin.common.guaranteeuse.GuaranteeUseBizEntryPlugin.loadGContract
        DataSet gmGuaranteeuse = QueryServiceHelper.queryDataSet(this.getClass().getName(), "gm_guaranteeuse", guaranteeuseSql, qFilter.toArray(), null);
        // 一个授信只有一个担保合同
//        gmGuaranteeuse = gmGuaranteeuse.groupBy(new String[]{"gsrcbillid", "gcontract.guaranteeorgtext"}).sum("gamount").finish();

        DataSet dataSet1 = ds.copy().filter("loanbillId is not null");
        DataSet dataSet2 = ds.copy().filter("payablebillId is not null");
        DataSet dataSet3 = ds.copy().filter("loanbillId is null and payablebillId is null"); // 有担保无债务

        dataSet1 = dataSet1.leftJoin(gmGuaranteeuse).on("loanbillId", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gamount as nckd_orgamount4", "a_guaranteeorgtext as nckd_otherorgname"}).finish();
        dataSet2 = dataSet2.leftJoin(gmGuaranteeuse).on("payablebillId", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gamount as nckd_orgamount4", "a_guaranteeorgtext as nckd_otherorgname"}).finish();
        dataSet3 = dataSet3.leftJoin(gmGuaranteeuse).on("id", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gamount as nckd_orgamount4", "a_guaranteeorgtext as nckd_otherorgname"}).finish();

        return dataSet1.union(dataSet2, dataSet3);
    }

    /**
     * 设置 建材集团担保	股份公司担保	南方万年青担保
     *
     * @param orgType
     * @param ds
     * @return
     */
    private DataSet setGuaranteeinfoAmount(String orgType, DataSet ds) {
        String guaranteeorg = null;
        if (orgType.equals("1")) {
            guaranteeorg = "C0";
        } else if (orgType.equals("2")) {
            guaranteeorg = "C01";
        } else if (orgType.equals("3")) {
            guaranteeorg = "C010301";
        }

        DynamicObject dynamicObject = BusinessDataServiceHelper.loadSingle("bos_org", new QFilter[]{new QFilter("number", QCP.equals, guaranteeorg)});

        QFilter qFilter = new QFilter("gcontract.entry_guaranteeorg.a_guaranteeorgtext", QCP.equals, dynamicObject.getString("name"));
//                .and("gsrcbilltype", QCP.not_equals, "cfm_creditlimit");
        // 担保占用
        String guaranteeuseSql = "id,gsrcbillid,gcontract,gcontract.entry_guaranteeorg.a_guaranteeorgtext,gamount,gratio,gdebtamount";
        // kd.tmc.fbp.formplugin.common.guaranteeuse.GuaranteeUseBizEntryPlugin.loadGContract
        DataSet gmGuaranteeuse = QueryServiceHelper.queryDataSet(this.getClass().getName(), "gm_guaranteeuse", guaranteeuseSql, qFilter.toArray(), null);
        // 一个授信只有一个担保合同
//        gmGuaranteeuse = gmGuaranteeuse.groupBy(new String[]{"gsrcbillid"}).sum("gamount").finish();

        DataSet dataSet1 = ds.copy().filter("loanbillId is not null");
        DataSet dataSet2 = ds.copy().filter("payablebillId is not null");
        DataSet dataSet3 = ds.copy().filter("loanbillId is null and payablebillId is null"); // 有担保无债务

        dataSet1 = dataSet1.leftJoin(gmGuaranteeuse).on("loanbillId", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gamount as nckd_orgamount" + orgType, "gcontract.entry_guaranteeorg.a_guaranteeorgtext as guaranteeorgtext" + orgType}).finish();
        dataSet2 = dataSet2.leftJoin(gmGuaranteeuse).on("payablebillId", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gamount as nckd_orgamount" + orgType, "gcontract.entry_guaranteeorg.a_guaranteeorgtext as guaranteeorgtext" + orgType}).finish();
        dataSet3 = dataSet3.leftJoin(gmGuaranteeuse).on("id", "gsrcbillid").select(ds.getRowMeta().getFieldNames(), new String[]{"gamount as nckd_orgamount" + orgType, "gcontract.entry_guaranteeorg.a_guaranteeorgtext as guaranteeorgtext" + orgType}).finish();

        return dataSet1.union(dataSet2, dataSet3);
    }

    /**
     * @param currentDate 当前日期
     * @param credittype  授信类别
     * @param ds
     * @return
     */
    private DataSet setBdFinorginfoAmount(Date currentDate, String credittype, DataSet ds) {
        QFilter qFilter = new QFilter("status", QCP.equals, "C")
//                .and("enddate", QCP.large_than, currentDate)
                .and("isclose", QCP.equals, "0");
        DataSet cfmCreditlimit = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cfm_creditlimit", "id,entry_type.id,entry_type.t_credittype,entry_type.t_totalamt", qFilter.toArray(), null);

        // 授信类别
        QFilter qFilter1 = new QFilter("number", QCP.equals, credittype);
        DynamicObject cfm_credittype = BusinessDataServiceHelper.loadSingle("cfm_credittype", qFilter1.toArray());

        ORM orm = ORM.create();
        DynamicObjectCollection creditlimit = orm.toPlainDynamicObjectCollection(cfmCreditlimit);
        List<DynamicObject> collect = creditlimit.stream().filter(t -> {
            if (t.getLong("entry_type.t_credittype") != 0) {
                // 授信类别 多选基础资料
                StringBuffer stringBuffer = new StringBuffer("select t.fbasedataid from t_cfm_creditlimit_detai_d t where t.fentryid = ?");
                DataSet dataSet = DB.queryDataSet("t_cfm_creditlimit_detai_d", new DBRoute("tmc"), stringBuffer.toString(), new Object[]{t.getLong("entry_type.t_credittype")});
                DynamicObjectCollection dynamicObjects = orm.toPlainDynamicObjectCollection(dataSet);
                long fbasedataid = dynamicObjects.get(0).getLong("fbasedataid");
                if (fbasedataid == cfm_credittype.getLong("id")) {
                    return true;
                }
            }
            return false;
        }).collect(Collectors.toList());

        DynamicObjectCollection dynamicObjects = new DynamicObjectCollection();
        dynamicObjects.addAll(collect);

        cfmCreditlimit = buildDataByObjCollection("algoKey", cfmCreditlimit.getRowMeta().getFields(), dynamicObjects);
        cfmCreditlimit = cfmCreditlimit.groupBy(new String[]{"id"}).sum("entry_type.t_totalamt").finish();

        // 字段标识
        String s = null;
        if (credittype.equals("SX0002")) {
            // 流动资金贷款
            s = "nckd_amount1";
        } else if (credittype.equals("SX0003")) {
            // 银行承兑汇票
            s = "nckd_amount2";
        }

        ds = ds.leftJoin(cfmCreditlimit).on("id", "id").select(ds.getRowMeta().getFieldNames(), new String[]{"entry_type.t_totalamt as " + s}).finish();

        return ds;
    }

    private QFilter buildFilter(ReportQueryParam reportQueryParam) {
        // 查询条件
        FilterInfo filterInfo = reportQueryParam.getFilter();

        QFilter qFilter = new QFilter("status", QCP.equals, "C")
//                .and("enddate", QCP.large_than, this.getCurrentDate())
                .and("isclose", QCP.equals, "0");

        // 借款单位
        FilterItemInfo org = filterInfo.getFilterItem("nckd_org");
        // 查询日期
        FilterItemInfo querydate = filterInfo.getFilterItem("nckd_querydate");
        // 金融机构
        FilterItemInfo mulbank = filterInfo.getFilterItem("nckd_mulbank");

        // 借款单位
        if (org.getValue() != null) {
            qFilter = qFilter.and(new QFilter("org", QCP.in, ((DynamicObjectCollection) org.getValue())
                    .stream()
                    .map(obj -> obj.getLong("id"))
                    .collect(Collectors.toList())));
        }
        // 金融机构
        if (mulbank.getValue() != null) {
            qFilter = qFilter.and(new QFilter("bank", QCP.in, ((DynamicObjectCollection) mulbank.getValue())
                    .stream()
                    .map(obj -> obj.getLong("id"))
                    .collect(Collectors.toList())));
        }

        return qFilter;
    }

    //DynamicObjectCollection 转换为 DataSet 方法
    public DataSet buildDataByObjCollection(String algoKey, kd.bos.algo.Field[] rowFields, DynamicObjectCollection objCollection) {
        DataSetBuilder dataSetBuilder = Algo.create(algoKey + ".emptyFields")
                .createDataSetBuilder(new RowMeta(rowFields));
        for (DynamicObject arObj : objCollection) {
            Object[] rowData = new Object[rowFields.length];
            for (int i = 0; i < rowFields.length; i++) {
                kd.bos.algo.Field field = rowFields[i];
                rowData[i] = arObj.get(field.getName());
            }
            dataSetBuilder.append(rowData);
        }
        return dataSetBuilder.build();
    }

    private Date getCurrentDate() {
        Calendar cal = new GregorianCalendar();
//        cal.set(11, 0);
//        cal.set(12, 0);
//        cal.set(13, 0);
//        cal.set(14, 0);
        return cal.getTime();
    }
}
